﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using DBUtil;
using ZXCompany.Models.Entity;
namespace ZXCompany.Models.Dao
{
    public class ZX_LeaveMassageDao
    {
        private Database db = new Database();
        private List<Entity.ZX_LeaveMassage> GetListFromDT(DataTable dt)
        {
            List<Entity.ZX_LeaveMassage> lexpert = new List<Entity.ZX_LeaveMassage>();
            foreach (DataRow dr in dt.Rows)
            {
        //         lname { get; set; }
        //public string contact { get; set; }
        //public string companyName { get; set; }
        //public string message { get; set; }
        //public string isRead
                Entity.ZX_LeaveMassage expert = new Entity.ZX_LeaveMassage();
                expert.ID = Convert.ToInt32(dr["ID"]);
                expert.lname = dr["lname"].ToString();
                expert.contact = dr["contact"].ToString();
                expert.companyName = dr["companyName"].ToString();
                expert.message = dr["message"].ToString();
                expert.isRead = dr["isRead"].ToString();
                expert.updateTime = dr["updateTime"].ToString();
                expert.addTime = dr["addTime"].ToString();
                lexpert.Add(expert);
            }
            return lexpert;
        }
        /// <summary>
        /// 获取在线留言
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public List<Entity.ZX_LeaveMassage> Select(SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull([ID],0) as [ID]
                            ,isnull([lname],'') as [lname]
                            ,isnull([contact],'') as [contact]
                            ,isnull([companyName],'') as [companyName]
                            ,isnull([message],'') as [message]
                            ,isnull([isRead],'') as [isRead]
                            ,isnull([addTime],'') as [addTime]
                            ,isnull([updateTime],'') as [updateTime]
                        FROM [ZX_LeaveMassage]";
                DataTable dt = db.Squery(sql, sconn);
                return GetListFromDT(dt);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// 通过ID获取在线留言
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public List<Entity.ZX_LeaveMassage> SelectById(int etid, SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull([ID],0) as [ID]
                            ,isnull([lname],'') as [lname]
                            ,isnull([contact],'') as [contact]
                            ,isnull([companyName],'') as [companyName]
                            ,isnull([message],'') as [message]
                            ,isnull([isRead],'') as [isRead]
                            ,isnull([addTime],'') as [addTime]
                            ,isnull([updateTime],'') as [updateTime]
                        FROM [ZX_LeaveMassage] where id=@id";
                SqlParameter[] para = new SqlParameter[]{
                    new SqlParameter("@id",etid)
                };
                DataTable dt = db.Squery(sql, sconn, para);
                return GetListFromDT(dt);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// <summary>
        /// 通过ID删除在线留言
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Delete(int etid, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                string sql = @"delete FROM [ZX_LeaveMassage] where id=@id";
                SqlParameter[] para = new SqlParameter[]{
                    new SqlParameter("@id",etid)
                };
                DataTable dt = db.Squery(sql, sconn, stran, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过ID更新在线留言
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Update(Entity.ZX_LeaveMassage Entype, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                string sql = @"Update ZX_LeaveMassage set isRead =@isRead,updateTime=@updateTime where id=@id";
                SqlParameter[] para = new SqlParameter[]{
                    new SqlParameter("@id",Entype.ID),
                    new SqlParameter("@isRead",Entype.isRead),
                    new SqlParameter("@updateTime",Entype.updateTime)
                };
                DataTable dt = db.Squery(sql, sconn, stran, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过ID更新在线留言
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Insert(Entity.ZX_LeaveMassage Entype, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                string sql = @"Insert into ZX_LeaveMassage (lname,contact,companyName,message,isRead,updateTime,addTime) values
                             (@lname,@contact,@companyName,@message,@isRead,@updateTime,@addTime)";
                SqlParameter[] para = new SqlParameter[]{
                    new SqlParameter("@lname",Entype.lname),
                    new SqlParameter("@contact",Entype.contact),
                    new SqlParameter("@companyName",Entype.companyName),
                    new SqlParameter("@message",Entype.message),
                    new SqlParameter("@isRead",Entype.isRead),
                    new SqlParameter("@updateTime",Entype.updateTime),
                    new SqlParameter("@addTime",Entype.addTime)
                };
                db.Udaquery(sql, sconn, stran, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}